import os
import re
import tkinter as tk
from tkinter import filedialog

def generate_validation_sql(statement):
    # 使用正则表达式解析语句
    create_table_pattern = re.compile(r'CREATE\s+TABLE\s+([a-zA-Z_]+\.[a-zA-Z_]+)\s*\((.*?)\);', re.IGNORECASE | re.DOTALL)
    alter_table_pattern = re.compile(r'ALTER\s+TABLE\s+([a-zA-Z_]+\.[a-zA-Z_]+)\s+ADD\s+([a-zA-Z_]+)\s+\w+;', re.IGNORECASE)
    insert_pattern = re.compile(r'INSERT\s+INTO\s+([a-zA-Z_]+\.[a-zA-Z_]+)\s*\((.*?)\)\s*VALUES\s*\((.*?)\);', re.IGNORECASE | re.DOTALL)
    delete_pattern = re.compile(r'DELETE\s+FROM\s+([a-zA-Z_]+\.[a-zA-Z_]+)\s+WHERE\s+(.*?);', re.IGNORECASE | re.DOTALL)
    update_pattern = re.compile(r'UPDATE\s+([a-zA-Z_]+\.[a-zA-Z_]+)\s+SET\s+((?:\s*[a-zA-Z_]+\s*=\s*\S+\s*,\s*)+\s*[a-zA-Z_]+\s*=\s*\S+)\s+WHERE\s+(.*?);', re.IGNORECASE | re.DOTALL)

    create_table_match = create_table_pattern.match(statement)
    alter_table_match = alter_table_pattern.match(statement)
    insert_match = insert_pattern.match(statement)
    delete_match = delete_pattern.match(statement)
    update_match = update_pattern.match(statement)

    if create_table_match:
        table_name = create_table_match.group(1)
        columns = create_table_match.group(2)
        validation_sql = f"SELECT * FROM {table_name};"
        return validation_sql
    elif alter_table_match:
        table_name = alter_table_match.group(1)
        column_name = alter_table_match.group(2)
        validation_sql = f"SELECT {column_name} FROM {table_name};"
        return validation_sql
    elif insert_match:
        table_name = insert_match.group(1)
        validation_sql = f"SELECT * FROM {table_name};"
        return validation_sql
    elif delete_match:
        table_name = delete_match.group(1)
        validation_sql = f"SELECT * FROM {table_name};"
        return validation_sql
    elif update_match:
        table_name = update_match.group(1)
        validation_sql = f"SELECT * FROM {table_name};"
        return validation_sql
    else:
        return "无法解析语句，请确保输入的是有效的DDL或DML语句。"

def read_sql_file(file_path):
    with open(file_path, 'r', encoding='utf-8') as file:
        sql_content = file.read()
    return sql_content

def select_sql_file():
    root = tk.Tk()
    root.withdraw()  # 隐藏主窗口

    file_path = filedialog.askopenfilename(
        title="选择 SQL 文件",
        filetypes=[("SQL 文件", "*.sql"), ("所有文件", "*.*")],
    )

    return file_path

def append_to_file(file_path, content):
    with open(file_path, 'a',encoding='UTF-8') as file:
        file.write(content + "\n")  # 添加换行符

# 获取当前脚本所在目录
script_directory = os.path.dirname(os.path.abspath(__file__))

# 选择 SQL 文件
sql_file_path = select_sql_file()

if sql_file_path:
    # 获取验证 SQL 文件的路径
    validation_file_path = os.path.splitext(sql_file_path)[0] + "_validation.sql"

    # 从 SQL 文件中读取内容
    sql_content = read_sql_file(sql_file_path)

    # 使用正则表达式匹配 DDL 和 DML 语句并生成验证 SQL
    statements = re.findall(r'(CREATE\s+TABLE\s+.*?;|ALTER\s+TABLE\s+.*?;|INSERT\s+INTO\s+.*?;|DELETE\s+FROM\s+.*?;|UPDATE\s+.*?;)', sql_content, re.IGNORECASE | re.DOTALL)
    for statement in statements:
        validation_sql = generate_validation_sql(statement)
        print("原始语句:", statement)
        print("验证 SQL:", validation_sql)

        # 将验证 SQL 内容追加到文件
        append_to_file(validation_file_path, validation_sql)
        print(f"验证 SQL 已保存到: {validation_file_path}\n")

else:
    print("用户取消选择文件。")
